using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;
using DTN.Core.Common;
namespace com.dtn.deals
{
	public class tbl_ServicesManager : Itbl_ServicesManager
	{
		private static Itbl_ServicesManager itbl_ServicesManager = null;
		private tbl_ServicesManager()
		{
		}
		public static Itbl_ServicesManager CreateInstance()
		{
			if (itbl_ServicesManager == null)
			{
				itbl_ServicesManager = new tbl_ServicesManager();
			}
			return itbl_ServicesManager;
		}
		public int Inserttbl_Services(tbl_Services tbl_services)
		{
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@name", SqlDbType.NVarChar),
																new SqlParameter("@partnerId", SqlDbType.Int),
                                                                new SqlParameter("@price", SqlDbType.Float),
																new SqlParameter("@priceUsd", SqlDbType.Float),
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tbl_services.name;
            parameters[2].Value = tbl_services.PartnerId;
            parameters[3].Value = tbl_services.Price;
            parameters[4].Value = tbl_services.PriceUsd;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_Service", CommandType.StoredProcedure, parameters);
                tbl_services.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tbl_services.ID = -1;
            }
            return tbl_services.ID;
		}
		public bool Updatetbl_Services(tbl_Services tbl_services)
		{
			bool bresult = false;
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@name", SqlDbType.NVarChar),
                                                                new SqlParameter("@price", SqlDbType.Float),
																new SqlParameter("@priceUsd", SqlDbType.Float),
															};
			parameters[0].Value = tbl_services.ID;
			parameters[1].Value = tbl_services.name;
			parameters[2].Value = tbl_services.Price;
            parameters[3].Value = tbl_services.PriceUsd;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_Service", CommandType.StoredProcedure, parameters);
				bresult = true;
			}
			catch (Exception ex)
			{
				bresult = false;
			}
			return bresult;
		}
		public bool Deletetbl_Services(int id)
		{
			bool bresult = false;
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
			parameters[0].Value = id;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Deletetbl_Service", CommandType.StoredProcedure, parameters);
				bresult = true;
			}
			catch (Exception ex)
			{
				bresult = false;
			}
			return bresult;
		}
		public tbl_Services Selecttbl_ServicesByID(int id)
		{
            IDataReader dr;
            tbl_Services tbl_services = new tbl_Services();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                dr = dataProvider.ExecuteReader("sp_Selecttbl_Service", CommandType.StoredProcedure, parameters);
                while (dr.Read())
                {
                    tbl_services.ID = Convert.ToInt32(dr["ID"].ToString());
                    tbl_services.name = (dr["name"].ToString());
                    tbl_services.cDate = Convert.ToDateTime(dr["cDate"].ToString());
                    tbl_services.PartnerId = int.Parse(dr["partnerId"].ToString());
                    tbl_services.Price = double.Parse(dr["price"].ToString());
                    tbl_services.PriceUsd = double.Parse(dr["priceUsd"].ToString());
                    tbl_services.isDel = int.Parse(dr["isDel"].ToString());
                }

            }
            catch (Exception ex)
            {
                return null;
            }
            return tbl_services;
		}
		public DataTable Selecttbl_ServicessDynamic(string Where, string Order)
		{
			DataSet ds = new DataSet();
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
			parameters[0].Value = Where;
			parameters[1].Value = Order;
			try
			{
				ds = dataProvider.ExecuteDataSet("sp_Selecttbl_ServicesDynamic", CommandType.StoredProcedure, parameters);
			}
			catch (Exception ex)
			{
				return null;
			}
			return ds.Tables["Table"];
		}

        //Cuong Code
        public static DataTable GetAllByTop(int partnerid, int top)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Services_GetAllByTop", dbConn);
            dbCmd.Parameters.AddWithValue("@top", top);
            dbCmd.Parameters.AddWithValue("@PartnerId", partnerid);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }


        public DataTable Selecttbl_ServicessPageDynamic(int StartIndex, int pagesize, string where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = pagesize  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_selecttbl_ServiceWithPageDynamic", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        public static DataTable GetTopDealByTF(int iStart, int iEnd)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Product_GetTopSaleInDay_ByTime", dbConn);
            dbCmd.Parameters.AddWithValue("@StartTime", iStart);
            dbCmd.Parameters.AddWithValue("@EndTime", iEnd);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetAllCateDealByTF(int iStart, int iEnd)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Product_SelectCatID_InDay_ByTime", dbConn);
            dbCmd.Parameters.AddWithValue("@StartTime", iStart);
            dbCmd.Parameters.AddWithValue("@EndTime", iEnd);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetTopDealByTFAnd_CatId(int iStart, int iEnd, int CatId)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Product_GetTopSaleInDay_ByTime_And_CatId", dbConn);
            dbCmd.Parameters.AddWithValue("@StartTime", iStart);
            dbCmd.Parameters.AddWithValue("@EndTime", iEnd);
            dbCmd.Parameters.AddWithValue("@CatId", CatId);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

    }
}
